﻿Imports System.Data.SQLite

Structure SplitTrans
    Dim SplitTransCat As Integer
    Dim SplitTransSubCat As Integer
    Dim SplitTransAmt As Double '*// S
End Structure

Structure QIF_Record
    Dim AccountID As Integer
    Dim Type As String          '*// !
    Dim dtTransaction As Date   '*// D
    Dim Amount As Double        '*// T
    Dim TransCode As String
    Dim Payee As String         '*// P
    Dim PayeeID As Integer
    Dim N As String             '*// N
    Dim Category As String      '*// L
    Dim CategID As Integer
    Dim SubCategID As Integer
    Dim Memo As String          '*// M
    Dim Status As String        '*// C
    Dim Number As String        '*// N
    Dim TransferAccountID As String
    Dim AmountTransfer As Double
    Dim SplitTr() As SplitTrans
    Dim SplitTrCount As Integer
End Structure

Module mdlImportQIF

    Private m_QIF_Record As New QIF_Record

    Public Sub fProcessQIFtoMMEX(sFileSource As String, AccountName As String, conSQLite As SQLiteConnection)

        Dim sFileReader As IO.StreamReader
        Dim sInputLine As String
        Dim iAccountID As Integer = fGetAccountID(AccountName, conSQLite)
        Dim iPayeeCash As Integer = fGetPayeeID("Cash", conSQLite)

        Dim dtTemp() As String

        'Dim iTOAccountID As Integer = fGetAccountID(AccountName, conSQLite)

        DefaultValues()
        '*// Default Account
        m_QIF_Record.AccountID = iAccountID
        '*// Dafault Payee
        m_QIF_Record.PayeeID = iPayeeCash


        If Dir(sFileSource.ToString) <> "" Then
            sFileReader = System.IO.File.OpenText(sFileSource)
            sInputLine = sFileReader.ReadLine()

            Do Until sInputLine Is Nothing

                If sInputLine = "^" Then

                    '*// Process Record
                    AddRecord(conSQLite)

                    Application.DoEvents()

                    '*// Empty all fields
                    DefaultValues()

                    '*// Default Account
                    m_QIF_Record.AccountID = iAccountID
                    '*// Dafault Payee
                    m_QIF_Record.PayeeID = iPayeeCash

                End If

                Select Case sInputLine.Substring(0, 1)
                    Case "!"
                        m_QIF_Record.AccountID = fGetAccountID(AccountName, conSQLite)
                        m_QIF_Record.Type = sInputLine.Substring(1)

                    Case "D"
                        dtTemp = sInputLine.Substring(1).Split("/")
                        m_QIF_Record.dtTransaction = dtTemp(2) & "-" & dtTemp(1) & "-" & dtTemp(0)
                        'Debug.Print("")

                    Case "T"
                        If IsNumeric(sInputLine.Substring(1)) Then
                            m_QIF_Record.Amount = sInputLine.Substring(1).Replace(".", ",")
                            If m_QIF_Record.Amount <= 0 Then
                                m_QIF_Record.TransCode = "Withdrawal"
                                m_QIF_Record.Amount = Math.Abs(m_QIF_Record.Amount)
                            Else
                                m_QIF_Record.TransCode = "Deposit"
                            End If

                        End If

                    Case "P"
                        m_QIF_Record.Payee = sInputLine.Substring(1)
                        m_QIF_Record.PayeeID = fGetPayeeID(m_QIF_Record.Payee, conSQLite)

                    Case "N"
                        m_QIF_Record.N = sInputLine.Substring(1)

                    Case "L"
                        If sInputLine.Contains("L[") And sInputLine.Contains("]") Then
                            '*// Transfers
                            If m_QIF_Record.TransCode = "Withdrawal" Then
                                m_QIF_Record.TransferAccountID = fGetAccountID(sInputLine.Substring(2, sInputLine.Length - 3), conSQLite)
                            Else
                                m_QIF_Record.TransferAccountID = m_QIF_Record.AccountID
                                m_QIF_Record.AccountID = fGetAccountID(sInputLine.Substring(2, sInputLine.Length - 3), conSQLite)
                            End If


                            m_QIF_Record.TransCode = "Transfer"
                            m_QIF_Record.PayeeID = -1
                            m_QIF_Record.AmountTransfer = Math.Abs(m_QIF_Record.Amount)
                        Else
                            m_QIF_Record.Category = sInputLine.Substring(1)

                            If m_QIF_Record.Category <> "" Then

                                If m_QIF_Record.Category.Contains(":") Then
                                    dtTemp = m_QIF_Record.Category.Split(":")
                                    m_QIF_Record.CategID = fGetCategID(dtTemp(0), conSQLite)
                                    m_QIF_Record.SubCategID = fGetSubCategID(dtTemp(1), m_QIF_Record.CategID, conSQLite)
                                Else
                                    m_QIF_Record.CategID = fGetCategID(m_QIF_Record.Category, conSQLite)
                                End If

                            End If

                        End If

                    Case "M"
                        m_QIF_Record.Memo = sInputLine.Substring(1).Replace(",", ".")

                    Case "C"
                        m_QIF_Record.Status = sInputLine.Substring(1)

                    Case "N"
                        m_QIF_Record.Number = sInputLine.Substring(1)

                    Case "S"
                        If sInputLine.Substring(1) <> "" Then

                            m_QIF_Record.SplitTrCount += 1
                            ReDim Preserve m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount)

                            If sInputLine.Substring(1).Contains(":") Then
                                dtTemp = sInputLine.Substring(1).Split(":")
                                m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransCat = fGetCategID(dtTemp(0), conSQLite)
                                m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransSubCat = fGetSubCategID(dtTemp(1), m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransCat, conSQLite)
                            Else
                                m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransCat = fGetCategID(sInputLine.Substring(1), conSQLite)
                                m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransSubCat = -1
                            End If

                        Else
                            m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransCat = -1
                            m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransSubCat = -1
                        End If

                    Case "$"
                        m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransAmt = sInputLine.Substring(1).Replace(".", ",")
                        m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransAmt = Math.Abs(m_QIF_Record.SplitTr(m_QIF_Record.SplitTrCount).SplitTransAmt)

                    Case Else
                        '*// Skip line

                End Select


                '*// Nextline
                sInputLine = sFileReader.ReadLine()
            Loop
        End If

    End Sub


    Private Sub AddRecord(conSQLite As SQLiteConnection)

        Dim sqlCommand As New SQLiteCommand("", conSQLite)

        If fGetCheckingAccount(conSQLite) = -1 Then

            sqlCommand.CommandText = "INSERT INTO [CHECKINGACCOUNT_V1]" _
                                    & " ([ACCOUNTID], [TOACCOUNTID], [PAYEEID], [TRANSCODE], [TRANSAMOUNT], [STATUS], [TRANSACTIONNUMBER], [NOTES]" _
                                        & ", [CATEGID], [SUBCATEGID], [TRANSDATE], [FOLLOWUPID], [TOTRANSAMOUNT])" _
                                    & " VALUES" _
                                            & " (" _
                                            & " " & m_QIF_Record.AccountID _
                                            & " ," & m_QIF_Record.TransferAccountID _
                                            & " ," & m_QIF_Record.PayeeID _
                                            & " ,'" & m_QIF_Record.TransCode & "'" _
                                            & " ," & m_QIF_Record.Amount.ToString.Replace(",", ".") _
                                            & " ,'F'" _
                                            & " ,'" & m_QIF_Record.Number & "'" _
                                            & " ,'" & m_QIF_Record.Memo.Replace("'", "''") & "'" _
                                            & " ," & m_QIF_Record.CategID _
                                            & " ," & m_QIF_Record.SubCategID _
                                            & " ,'" & m_QIF_Record.dtTransaction.ToString("yyyy-MM-dd") & "'" _
                                            & " ,-1" _
                                            & " ," & m_QIF_Record.AmountTransfer.ToString.Replace(",", ".") _
                                            & " )"
            sqlCommand.ExecuteNonQuery()

        Else
            'TODO: Add to LOG


        End If


        '*// Are there split transactions?
        If m_QIF_Record.SplitTrCount > 0 Then

            For i = 1 To m_QIF_Record.SplitTrCount

                sqlCommand.CommandText = "INSERT INTO SPLITTRANSACTIONS_V1" _
                                    & " (TRANSID,CATEGID,SUBCATEGID,SPLITTRANSAMOUNT)" _
                                & " VALUES" _
                                     & " (" _
                                        & " " & fGetCheckingAccount(conSQLite) _
                                        & " ," & m_QIF_Record.SplitTr(i).SplitTransCat _
                                        & " ," & m_QIF_Record.SplitTr(i).SplitTransSubCat _
                                        & " ," & m_QIF_Record.SplitTr(i).SplitTransAmt.ToString.Replace(",", ".") _
                                    & " )"

                sqlCommand.ExecuteNonQuery()

            Next i

        End If



    End Sub

    Private Function fGetCheckingAccount(conSQLite As SQLiteConnection) As Integer

        Dim da As New SQLite.SQLiteDataAdapter
        Dim ds As New DataSet

        Dim sqlCommand As New SQLiteCommand("", conSQLite)
        Dim CommandText As String = ""
        Dim iReturnTransID As Integer = -1 '*// Default Value = Nothing found

        '*// If this is a transfer, check if this exists
        'If m_QIF_Record.TransCode <> "Transfer" Then
        CommandText = "SELECT TRANSID FROM CHECKINGACCOUNT_V1 WHERE ACCOUNTID = " & m_QIF_Record.AccountID _
                                        & " AND TOACCOUNTID = " & m_QIF_Record.TransferAccountID _
                                        & " AND TRANSCODE = '" & m_QIF_Record.TransCode & "'" _
                                        & " AND TRANSAMOUNT = " & m_QIF_Record.Amount.ToString.Replace(",", ".") _
                                        & " AND TRANSDATE = '" & m_QIF_Record.dtTransaction.ToString("yyyy-MM-dd") & "'" _
                                        & " AND TOTRANSAMOUNT = " & m_QIF_Record.AmountTransfer.ToString.Replace(",", ".") _
                                        & " AND PAYEEID = " & m_QIF_Record.PayeeID _
                                        & " AND CATEGID = " & m_QIF_Record.CategID _
                                        & " AND SUBCATEGID = " & m_QIF_Record.SubCategID

        da = New SQLiteDataAdapter(CommandText, conSQLite)
        da.Fill(ds, "CHECKINGACCOUNT_V1")

        If ds.Tables(0).Rows.Count <> 0 Then

            iReturnTransID = ds.Tables(0).Rows(0)("TRANSID")
            Return iReturnTransID

        End If

        '*// If there are 2 accounts with different currencies
        'TODO: Only allow this in the first 10 transactions from an account.
        If m_QIF_Record.TransCode = "Transfer" Then

            CommandText = "SELECT TRANSID FROM CHECKINGACCOUNT_V1 WHERE ACCOUNTID = " & m_QIF_Record.AccountID _
                            & " AND TOACCOUNTID = " & m_QIF_Record.TransferAccountID _
                            & " AND TRANSCODE = '" & m_QIF_Record.TransCode & "'" _
                            & " AND TRANSDATE = '" & m_QIF_Record.dtTransaction.ToString("yyyy-MM-dd") & "'" _
                            & " AND PAYEEID = " & m_QIF_Record.PayeeID _
                            & " AND CATEGID = " & m_QIF_Record.CategID _
                            & " AND SUBCATEGID = " & m_QIF_Record.SubCategID

            da = New SQLiteDataAdapter(CommandText, conSQLite)
            da.Fill(ds, "CHECKINGACCOUNT_V1")

            If ds.Tables(0).Rows.Count <> 0 Then

                iReturnTransID = ds.Tables(0).Rows(0)("TRANSID")   '-1

                sqlCommand.CommandText = "UPDATE CHECKINGACCOUNT_V1" _
                                        & " SET TOTRANSAMOUNT = " & m_QIF_Record.Amount.ToString.Replace(",", ".") _
                                        & " WHERE TRANSID = " & ds.Tables(0).Rows(0)("TRANSID")
                sqlCommand.ExecuteNonQuery()

            End If

        End If



        Return iReturnTransID
    End Function


    Private Function fGetAccountID(AccountName As String, conSQLite As SQLiteConnection) As Integer

        Dim da As New SQLite.SQLiteDataAdapter
        Dim ds As New DataSet

        Dim CommandText As String = "SELECT ACCOUNTID FROM ACCOUNTLIST_V1 WHERE ACCOUNTNAME = '" & AccountName.Replace("'", "''") & "'"


        da = New SQLiteDataAdapter(CommandText, conSQLite)
        da.Fill(ds, "ACCOUNTLIST_V1")

        If ds.Tables(0).Rows.Count = 0 Then

            CommandText = "INSERT INTO ACCOUNTLIST_V1 (ACCOUNTNAME, ACCOUNTTYPE, STATUS, FAVORITEACCT, CURRENCYID) VALUES" _
                                        & " (" _
                                        & " '" & AccountName.Replace("'", "''") & "'" _
                                        & " ,'Checking'" _
                                        & " ,'Open'" _
                                        & " ,'TRUE'" _
                                        & " ,2" _
                                        & " )"

            Dim sqlCommand As New SQLiteCommand(CommandText, conSQLite)
            sqlCommand.ExecuteNonQuery()

            Return fGetAccountID(AccountName, conSQLite)

        Else
            Return ds.Tables(0).Rows(0)("ACCOUNTID")
        End If


        Return -1
    End Function


    Private Function fGetPayeeID(PayeeName As String, conSQLite As SQLiteConnection) As Integer

        Dim da As New SQLite.SQLiteDataAdapter
        Dim ds As New DataSet

        Dim CommandText As String = "SELECT PAYEEID FROM PAYEE_V1 WHERE PAYEENAME = '" & PayeeName.Replace("'", "''") & "'"


        da = New SQLiteDataAdapter(CommandText, conSQLite)
        da.Fill(ds, "PAYEE_V1")

        If ds.Tables(0).Rows.Count = 0 Then

            CommandText = "INSERT INTO PAYEE_V1 (PAYEENAME, CATEGID, SUBCATEGID) VALUES" _
                                        & " (" _
                                        & " '" & PayeeName.Replace("'", "''") & "'" _
                                        & " ,-1" _
                                        & " ,-1" _
                                        & " )"

            Dim sqlCommand As New SQLiteCommand(CommandText, conSQLite)
            sqlCommand.ExecuteNonQuery()

            Return fGetPayeeID(PayeeName, conSQLite)

        Else
            Return ds.Tables(0).Rows(0)("PAYEEID")
        End If


        Return -1
    End Function

    Private Function fGetCategID(CategoryName As String, conSQLite As SQLiteConnection) As Integer

        Dim da As New SQLite.SQLiteDataAdapter
        Dim ds As New DataSet

        Dim CommandText As String = "SELECT CATEGID FROM CATEGORY_V1 WHERE CATEGNAME = '" & CategoryName.Replace("'", "''") & "'"


        da = New SQLiteDataAdapter(CommandText, conSQLite)
        da.Fill(ds, "CATEGORY_V1")

        If ds.Tables(0).Rows.Count = 0 Then

            CommandText = "INSERT INTO CATEGORY_V1 (CATEGNAME) VALUES" _
                                        & " (" _
                                        & " '" & CategoryName.Replace("'", "''") & "'" _
                                        & " )"

            Dim sqlCommand As New SQLiteCommand(CommandText, conSQLite)
            sqlCommand.ExecuteNonQuery()

            Return fGetCategID(CategoryName, conSQLite)

        Else
            Return ds.Tables(0).Rows(0)("CATEGID")
        End If


        Return -1
    End Function

    Private Function fGetSubCategID(SubCategoryName As String, CategID As Integer, conSQLite As SQLiteConnection) As Integer

        Dim da As New SQLite.SQLiteDataAdapter
        Dim ds As New DataSet

        Dim CommandText As String = "SELECT SUBCATEGID FROM SUBCATEGORY_V1" _
                                        & " WHERE SUBCATEGNAME = '" & SubCategoryName.Replace("'", "''") & "'" _
                                        & " AND CATEGID = " & CategID


        da = New SQLiteDataAdapter(CommandText, conSQLite)
        da.Fill(ds, "SUBCATEGORY_V1")

        If ds.Tables(0).Rows.Count = 0 Then

            CommandText = "INSERT INTO SUBCATEGORY_V1 (SUBCATEGNAME,CATEGID) VALUES" _
                                        & " (" _
                                        & " '" & SubCategoryName.Replace("'", "''") & "'" _
                                        & " ," & CategID _
                                        & " )"

            Dim sqlCommand As New SQLiteCommand(CommandText, conSQLite)
            sqlCommand.ExecuteNonQuery()

            Return fGetSubCategID(SubCategoryName, CategID, conSQLite)

        Else
            Return ds.Tables(0).Rows(0)("SUBCATEGID")
        End If


        Return -1
    End Function

    Private Sub DefaultValues()
        With m_QIF_Record
            .Type = ""
            .dtTransaction = "#12:00:00 AM#"
            .Amount = 0
            .Payee = ""
            .N = ""
            .Category = ""
            .Memo = ""
            .Status = ""
            .Number = ""
            .TransferAccountID = -1
            .TransCode = ""
            .PayeeID = -1
            .CategID = -1
            .SubCategID = -1
            .AmountTransfer = 0
            ReDim .SplitTr(0)
            .SplitTrCount = 0

        End With
    End Sub

End Module
